﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Esint.Common.Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;

namespace Esint.Common.Data.SqlDAL
{
    public  class RootSqlDataAccess
    {
  
        #region 初始化

        public RootSqlDataAccess()
        {
        }

        public string CurrentConn { get; set; }

        /// <summary>
        /// 连接字符串
        /// </summary>
        private static Dictionary<string, string> _connectString=new Dictionary<string,string>();

        public static Dictionary<string, string> ConnectString { get { return _connectString; } set{_connectString = value;} }

        
        /// <summary>
        /// 是否记录SQL日志
        /// </summary>
        public static bool IsRecordSqlLog
        {
            get
            {
                if (IsRecordSQL.ToUpper() == "TRUE")
                    return true;
                else
                    return false;
            }
        }

        public static string IsRecordSQL { get; set; }
        #endregion 

        #region 连接和事务

        /// <summary>
        /// 打开数据库链接操作
        /// </summary>
        /// <returns>OracleConnection</returns>
        public IDbConnection GetConnection()
        {
            //IDbConnection sqlConn = new SqlConnection(ConnectString[CurentConn]);
            IDbConnection sqlConn = new SqlConnection(ConnectString["Default"]);
            try
            {
                 sqlConn.Open();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return  sqlConn;
        }


        /// <summary>
        /// 建立数据库事务
        /// </summary>
        /// <returns></returns>
        public IDbTransaction GetTransaction()
        {
            IDbConnection _sqlconn = GetConnection();
            //return _sqlconn.BeginTransaction();
            return new DBTransWrapper(_sqlconn);
        }

        /// <summary>
        /// 关闭数据库链接 
        /// </summary>
        /// <param name="_oracleconn"></param>
        public void CloseConn(IDbConnection connect)
        {
            if (connect != null)
            {
                if (connect.State != ConnectionState.Closed)
                {
                    connect.Close();
                }
                connect.Dispose();
            }
        }

        /// <summary>
        /// 关闭事务
        /// </summary>
        /// <param name="trans"></param>
        public void CloseTrans(IDbTransaction trans)
        {
            if (trans != null)
            {
                trans.Dispose();
            }
        }

        #endregion 

        #region ExecuteSQL

        /// <summary>
        /// 执行sql语句做更新操作( Execute sql to update)
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="SQL">sql语句</param>
        /// <param name="paras">IDbParameters 参数</param>
        /// <returns></returns>
        public int ExecuteSQL(System.Data.IDbConnection conn, string SQL, List<IDataParameter> paras)
        {
            int returnValue = 0;
            try
            {
                SqlParameter[] sqlParameters = new SqlParameter[paras.Count];
                int i = 0;
                foreach (SqlParameter para in paras)
                {
                    sqlParameters[i] = para;
                    i++;
                }

                SQLLogInfo sqlLog = new SQLLogInfo(); 

                if (IsRecordSqlLog)
                {
                    sqlLog.Sqltext = GetRealSql(SQL, sqlParameters);
                    sqlLog.Starttime = DateTime.Now;
                }

                returnValue = SqlHelper.ExecuteNonQuery((SqlConnection)conn, CommandType.Text, SQL, sqlParameters);

                if (IsRecordSqlLog)
                {
                    sqlLog.End((SqlConnection)conn);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return returnValue;
        }

        #endregion 
        /// <summary>
        /// 重载无参数方法---王岩
        /// 2011-12-15
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="commandType"></param>
        /// <param name="SQL"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(IDbConnection conn, CommandType commandType, string SQL)
        {
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext = SQL;
                sqlLog.Starttime = DateTime.Now;
            }

            IDataReader dr = SqlHelper.ExecuteReader((SqlConnection)conn, commandType, SQL);

            if (IsRecordSqlLog)
            {
                using (IDbConnection logconn = GetConnection())
                {
                    sqlLog.End((SqlConnection)logconn);
                }
            }
            return dr;
        }

        /// <summary>
        /// 执行sql语句做更新操作( Execute sql to update)
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <param name="SQL">sql语句</param>
        /// <param name="paras">IDbParameters 参数</param>
        /// <returns></returns>
        public int ExecuteSQL(IDbTransaction trans, string SQL, List<IDataParameter> paras)
        {
            int returnValue = 0;
            try
            {
                SqlParameter[] sqlParameters = new SqlParameter[paras.Count];
                int i = 0;
                foreach (SqlParameter para in paras)
                {
                    sqlParameters[i] = para;
                    i++;
                }

                SQLLogInfo sqlLog = new SQLLogInfo();

                if (IsRecordSqlLog)
                {
                    sqlLog.Sqltext = GetRealSql(SQL, sqlParameters);
                    sqlLog.Starttime = DateTime.Now;
                }

                var temp = trans as DBTransWrapper;
                if (temp != null)
                {
                    returnValue = SqlHelper.ExecuteNonQuery((SqlTransaction)temp.SysTrans, CommandType.Text, SQL, sqlParameters);
                    if (IsRecordSqlLog)
                    {
                        sqlLog.End((SqlTransaction)temp.SysTrans);
                    }
                }
                else
                {
                    returnValue = SqlHelper.ExecuteNonQuery((SqlTransaction)trans, CommandType.Text, SQL, sqlParameters);
                    if (IsRecordSqlLog)
                    {
                        sqlLog.End((SqlTransaction)trans);
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return returnValue;
        }

        public IDataReader ExecuteReader(IDbConnection conn, CommandType commandType, string SQL, SqlParameter[] paras)
        {
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext = GetRealSql(SQL, paras);
                sqlLog.Starttime = DateTime.Now;
            }

            IDataReader dr =SqlHelper.ExecuteReader((SqlConnection)conn, commandType, SQL, paras);

            if (IsRecordSqlLog)
            {
                using (IDbConnection logconn = GetConnection())
                {
                    sqlLog.End((SqlConnection)logconn);
                }
            }
            return dr;
        }

        public IDataReader ExecuteReader(IDbConnection conn, CommandType commandType, IRootQueryObject queryObject, List<IRootWhereParameter> paras)
        {
            string SQL =GetSqlTextByQueryObject(queryObject);
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext =queryObject.ExecSQLCommandText;
                sqlLog.Starttime = DateTime.Now;
            }

            IDataReader dr = SqlHelper.ExecuteReader((SqlConnection)conn, commandType, SQL, GetDataParameters(paras));

            if (IsRecordSqlLog)
            {
                if (IsRecordSqlLog)
                {
                    using (IDbConnection logconn = GetConnection())
                    {
                        sqlLog.End((SqlConnection)logconn);
                    }
                }
            }
            return dr;
        }


        public IDataReader ExecuteReader(IDbTransaction trans, CommandType commandType, string SQL, SqlParameter[] paras)
        {
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext = GetRealSql(SQL, paras);
                sqlLog.Starttime = DateTime.Now;
            }
            IDataReader dr=null;
            var temp = trans as DBTransWrapper;
            if (temp != null)
            {
                dr = SqlHelper.ExecuteReader((SqlTransaction)temp.SysTrans, commandType, SQL, paras);
            }
            else
            {
                dr = SqlHelper.ExecuteReader((SqlTransaction)trans, commandType, SQL, paras);
            }

            if (IsRecordSqlLog)
            {
                if (IsRecordSqlLog)
                {
                    using (IDbConnection logconn = GetConnection())
                    {
                        sqlLog.End((SqlConnection)logconn);
                    }
                }
            }
            return dr;
        }

        public IDataReader ExecuteReader(IDbTransaction trans, CommandType commandType, IRootQueryObject queryObject, List<IRootWhereParameter> paras)
        {
            string SQL = GetSqlTextByQueryObject(queryObject);
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext = queryObject.ExecSQLCommandText;
                sqlLog.Starttime = DateTime.Now;
            }
            IDataReader dr;
            var temp = trans as DBTransWrapper;
            if (temp != null)
            {
                dr = SqlHelper.ExecuteReader((SqlTransaction)temp.SysTrans, commandType, SQL, GetDataParameters(paras));
            }
            else
            {
                dr = SqlHelper.ExecuteReader((SqlTransaction)trans, commandType, SQL, GetDataParameters(paras));
            }
            if (IsRecordSqlLog)
            {
                if (IsRecordSqlLog)
                {
                    using (IDbConnection logconn = GetConnection())
                    {
                        sqlLog.End((SqlConnection)logconn);
                    }
                }
            }
            return dr;
        }


        public DataSet ExecuteDataset(IDbConnection conn, CommandType commandType, IRootQueryObject queryObject, List<IRootWhereParameter> paras)
        {
            string SQL = GetSqlTextByQueryObject(queryObject);
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext = queryObject.ExecSQLCommandText;
                sqlLog.Starttime = DateTime.Now;
            }

            DataSet ds = SqlHelper.ExecuteDataset((SqlConnection)conn, commandType, SQL, GetDataParameters(paras));

            if (IsRecordSqlLog)
            {
                sqlLog.End((SqlConnection)conn);
            }
            return ds;
        
         }

        public DataSet ExecuteDataset(IDbTransaction trans, CommandType commandType, IRootQueryObject queryObject, List<IRootWhereParameter> paras)
        {
            string SQL = GetSqlTextByQueryObject(queryObject);
            SQLLogInfo sqlLog = new SQLLogInfo();
            if (IsRecordSqlLog)
            {
                sqlLog.Sqltext = queryObject.ExecSQLCommandText;
                sqlLog.Starttime = DateTime.Now;
            }
            DataSet ds;
            var temp = trans as DBTransWrapper;
            if (temp != null)
            {
                ds = SqlHelper.ExecuteDataset((SqlTransaction)temp.SysTrans, commandType, SQL, GetDataParameters(paras));
                if (IsRecordSqlLog)
                {
                    sqlLog.End((SqlTransaction)temp.SysTrans);
                }
            }
            else
            {
                ds = SqlHelper.ExecuteDataset((SqlTransaction)trans, commandType, SQL, GetDataParameters(paras));
                if (IsRecordSqlLog)
                {
                    sqlLog.End((SqlTransaction)trans);
                }
            }

            return ds;
        }

        //public object ExecuteScalar(IDbConnection conn, CommandType commandType, string SqlText, List<IRootWhereParameter> paras)
        //{
        //    object returnValue;
        //    try
        //    {
        //        SqlParameter[] sqlParameters = new SqlParameter[paras.Count];
        //        int i = 0;
        //        foreach (SqlParameter para in paras)
        //        {
        //            sqlParameters[i] = para;
        //            i++;
        //        }

        //        SQLLogInfo sqlLog = new SQLLogInfo();

        //        if (IsRecordSqlLog)
        //        {
        //            sqlLog.Sqltext = GetRealSql(SqlText, sqlParameters);
        //            sqlLog.Starttime = DateTime.Now;
        //        }

        //        returnValue =  SqlHelper.ExecuteScalar((SqlConnection)conn, commandType, SqlText, GetDataParameters(paras));

        //        if (IsRecordSqlLog)
        //        {
        //            sqlLog.End((SqlConnection)conn);
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception(e.Message);
        //    }
        //    return returnValue;
        //}



        public object ExecuteScalar(IDbConnection conn, CommandType commandType, string SqlText, List<IDataParameter> paras)
        {
            try
            {
                SqlParameter[] sqlParameter = new SqlParameter[paras.Count];
                int i = 0;
                foreach (SqlParameter para in paras)
                {
                    sqlParameter[i] = para;
                    i++;
                }
                return SqlHelper.ExecuteScalar((SqlConnection)conn, CommandType.Text, SqlText, sqlParameter);
            }
            catch (Exception e)
            {
                CloseConn(conn);
                throw new Exception(e.Message);

            }
        }

        public object ExecuteScalar(IDbTransaction trans, CommandType commandType, string SqlText, List<IDataParameter> paras)
        {
            try
            {
                SqlParameter[] sqlParameter = new SqlParameter[paras.Count];
                int i = 0;
                foreach (SqlParameter para in paras)
                {
                    sqlParameter[i] = para;
                    i++;
                }

                var temp = trans as DBTransWrapper;
                if (temp != null)
                {
                    return SqlHelper.ExecuteScalar((SqlTransaction)temp.SysTrans, CommandType.Text, SqlText, sqlParameter);
                }
                else
                {
                    return SqlHelper.ExecuteScalar((SqlTransaction)trans, CommandType.Text, SqlText, sqlParameter);
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        public int GetRecordCount(IDbConnection conn, CommandType commandType, IRootQueryObject queryObject, List<IRootWhereParameter> paras)
        {
            int recordNum = 0;
            queryObject.PageView.IsPageView = false;
           
            if (string.IsNullOrEmpty(queryObject.Alias))
            {
                queryObject.Alias = "a";
            }
            string oldSql = GetSqlTextByQueryObject(queryObject);
            int startx = oldSql.ToUpper().Replace(" ","").IndexOf("SELECTDISTINCT");
            if (startx != -1)
            {
                oldSql = oldSql.Substring(0, startx) + "SELECT distinct top 100 percent" + oldSql.Substring(startx + 15);
            }
            else
            {
                int startx1 = oldSql.ToUpper().Replace(" ", "").IndexOf("SELECT");
                if (startx1 != -1)
                {
                    oldSql = oldSql.Substring(0, startx1) + "SELECT  top 100 percent" + oldSql.Substring(startx1 + 6);
                }
            }
            string sql = "select count(*) As RecordCount from (" + oldSql+ ") "+queryObject.Alias;

            recordNum = (int)SqlHelper.ExecuteScalar((SqlConnection)conn, commandType, sql, GetDataParameters(paras));
           
            return recordNum;
        }


        public int ExecuteNonQuery(IDbConnection conn, CommandType commandType, string commandText, params SqlParameter[] parameterValues)
        {
            return SqlHelper.ExecuteNonQuery((SqlConnection)conn, commandType, commandText, parameterValues);
        }

        public int ExecuteNonQuery(IDbTransaction trans, CommandType commandType, string commandText, params SqlParameter[] parameterValues)
        {
            var temp = trans as DBTransWrapper;
            if (temp != null)
            {
                return SqlHelper.ExecuteNonQuery((SqlTransaction)temp.SysTrans, commandType, commandText, parameterValues);
            }
            else
            {
                return SqlHelper.ExecuteNonQuery((SqlTransaction)trans, commandType, commandText, parameterValues);
            }
        }

        public int GetRecordCount(IDbTransaction trans, CommandType commandType, IRootQueryObject queryObject, List<IRootWhereParameter> paras)
        {
            int recordNum = 0;
            queryObject.PageView.IsPageView = false;
            string sql = "select count(*) As RecordCount from (" + GetSqlTextByQueryObject(queryObject) + ") " + queryObject.Alias;

            var temp = trans as DBTransWrapper;
            if (temp != null)
            {
                recordNum = (int)SqlHelper.ExecuteScalar((SqlTransaction)temp.SysTrans, commandType, sql, GetDataParameters(paras));
            }
            else
            {
                recordNum = (int)SqlHelper.ExecuteScalar((SqlTransaction)trans, commandType, sql, GetDataParameters(paras));
            }
            return recordNum;
        }

        public  SqlParameter[] GetDataParameters(List<IRootWhereParameter> paras)
        {
            if (paras == null || paras.Count == 0)
                return null;

            SqlParameter[] parameters = new SqlParameter[paras.Count];
            int i = 0;
            foreach (IRootWhereParameter para in paras)
            {
                SqlParameter oraPara = new SqlParameter();
                oraPara.ParameterName = para.ParameterName;
                oraPara.Value = para.Value;
                parameters[i] = oraPara;
                i++;
            }
            return parameters;
        }

        internal string GetSqlTextByQueryObject(IRootQueryObject queryObject)
        {
            string sqlText = "";

            //如果是组合语句;
            if (!String.IsNullOrEmpty(queryObject.SQLText))
            {
                sqlText = queryObject.SQLText;
            }
            else
            {

                if (queryObject.PageView.IsPageView && string.IsNullOrEmpty(queryObject.Alias))
                    throw new ArgumentException("分页查询时,表" + queryObject.TableName + "的别名不能为空!");

                //
                // 拼接关联表查询元素 
                // 
                if (string.IsNullOrEmpty(queryObject.FieldString))
                    queryObject.FieldString = string.IsNullOrEmpty(queryObject.Alias) ? "*" : queryObject.Alias + ".*";

                string fieldList = queryObject.FieldString;

                string joinTableSql = "";

                foreach (IRootJoinTable joinTable in queryObject.JoinTables)
                {
                    if (string.IsNullOrEmpty(joinTable.Alias))
                        throw new ArgumentException("多表关联查询时,表" + joinTable.TableName + "的别名不能为空!");

                    if (string.IsNullOrEmpty(joinTable.JoinExpression))
                        throw new ArgumentException("多表关联时,表" + joinTable.TableName + "的关联表达式不能为空!");

                    fieldList += joinTable.FieldString;   //拼接查询字段

                    // 拼接Join 语句 
                    if (joinTable.WhereParameters == null || joinTable.WhereParameters.Count == 0)
                    {
                        joinTableSql += string.Format(" JOIN {0} {1} ON {2} ", joinTable.TableName, joinTable.Alias, joinTable.JoinExpression);
                    }
                    else
                    {
                        string whereExp = "";
                        foreach (IRootWhereParameter where in joinTable.WhereParameters)
                        {
                            whereExp += where.WhereExpression + " ";
                        }
                        joinTableSql += string.Format(" JOIN (SELECT * FROM {0} WHERE 1=1 {1}) {2} ON {3} ", joinTable.TableName, whereExp, joinTable.Alias, joinTable.JoinExpression);
                    }
                }

                string whereSql = "";
                foreach (IRootWhereParameter where in queryObject.WhereParameters)
                {
                    whereSql += " "+ where.WhereExpression + " ";
                }

                sqlText = "SELECT {0} FROM {1} {2} {3} WHERE 1=1 {4} ";

                if (!string.IsNullOrEmpty(queryObject.GroupByString))
                    sqlText += " GROUP BY " + queryObject.GroupByString;

                sqlText = string.Format(sqlText, fieldList, queryObject.TableName, queryObject.Alias, joinTableSql, whereSql);
            }

            //分页,添加分页代码
            if (queryObject.PageView.IsPageView)
            {
                int start_row_num = (queryObject.PageView.PageIndex - 1) * queryObject.PageView.PageSize + 1;
                int end_row_num = (queryObject.PageView.PageIndex) * queryObject.PageView.PageSize;

                if (string.IsNullOrEmpty(queryObject.OrderByString))
                {
                    sqlText = @"select * from (SELECT  *,ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId FROM  ( select *, 1 as orderbyID from ( " +
                    sqlText + ")  as  t1 )   as t2 ) as t3 where AllowPagingId between " + start_row_num + " and " + end_row_num.ToString();

                }
                else
                {
                    sqlText = @"select * from (SELECT ROW_NUMBER() OVER(Order By " + queryObject.OrderByString + @") AS AllowPagingId,* FROM (" +
                    sqlText + ") t) t1  where AllowPagingId between " + start_row_num + " and " + end_row_num;
                }

            }
            else
            {
                if (!string.IsNullOrEmpty(queryObject.OrderByString))
                    sqlText += " ORDER BY " + queryObject.OrderByString;

            }
            queryObject.ExecSQLCommandText = sqlText;
            foreach (WhereParameter para in queryObject.WhereParameters)
            {
                if (para.Value == null)
                   queryObject.ExecSQLCommandText= queryObject.ExecSQLCommandText.Replace(para.ParameterName, "''");
                else
                   queryObject.ExecSQLCommandText= queryObject.ExecSQLCommandText.Replace(para.ParameterName, "'"+para.Value.ToString()+"'");
            }
            return sqlText;
        }



        internal string GetRealSql(string sqlText, SqlParameter[] paras)
        {
            string str = sqlText;
            foreach (SqlParameter para in paras)
            {
                if (para.Value == null)
                    str = str.Replace(para.ParameterName, "''");
                else
                    str = str.Replace(para.ParameterName, "'" + para.Value.ToString() + "'");            
            }
            return str;
        }

        /// <summary>
        /// 获取登陆数据库用户名
        /// </summary>
        /// <returns></returns>
        internal  string GetOwer()
        {
            try
            {
                string strConn = ConnectString[CurrentConn];
                int iLocation = strConn.ToUpper().IndexOf("USER ID=") + 8;//起始位置
                int jLocation = strConn.ToUpper().IndexOf(";", iLocation);//截止位置
                int lengh;//数据库用户字符的长度
                if (jLocation > -1)
                    lengh = jLocation - iLocation;
                else
                    lengh = strConn.Length - iLocation;
                return strConn.ToUpper().Substring(iLocation, lengh);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 获得系统日期
        /// </summary>
        /// <param name="dateLength">需要得到的系统时间字符串的长度，8位或14位，8位格式为YYYYMMDD，14位格式为YYYYMMDDHHMMSS</param>
        /// <param name="dateType">时间格式，"0" YYYYMMDDhhmmss; "1" YYYY年MM月DD日hh:mm:ss</param>
        /// <returns></returns>
        public string GetSysDate()
        {
            IDbConnection conn = GetConnection();
            string dbDate = "";
            try
            {

                string strSql = "Select GETDATE() AS DBDATE";

                using (IDataReader dr = SqlHelper.ExecuteReader((SqlConnection)conn, CommandType.Text, strSql.ToString()))
                {
                    if (dr.Read())
                    {
                        dbDate = dr["DBDATE"].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConn(conn);
            }
            return dbDate;
        }
    }
}
